package com.mbc.common.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mbc.common.entities.CurrencyRate;

public class IAIMSCurrencyRateDAOImpls extends DAO<CurrencyRate> implements
		IAIMSCurrencyRateDAO {

	public IAIMSCurrencyRateDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "CurrencyRate";
	}

	@Override
	public void create(CurrencyRate entity) throws SQLException {
		query = "insert into "
				+ table_name
				+ " ( CurrencyRateKey, CurrencyRate_ID, Description, SourceCurrencyKey, ConvertCurrencyKey, Roper, Office_id) values( ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getCurrencyRateId());
		pstmt.setString(i++, entity.getCurrencyRateCode());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getSourceCurrencyId());
		pstmt.setLong(i++, entity.getConvertCurrencyId());
		pstmt.setString(i++, entity.getRoper());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(CurrencyRate entity) throws SQLException {
		query = "update "
				+ table_name
				+ " set  CurrencyRate_ID = ?, Description = ?, SourceCurrencyKey = ?, ConvertCurrencyKey = ?, Roper = ?"
				+ " where CurrencyRateKey = " + entity.getCurrencyRateId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getCurrencyRateCode());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getSourceCurrencyId());
		pstmt.setLong(i++, entity.getConvertCurrencyId());
		pstmt.setString(i++, entity.getRoper());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where CurrencyRateKey = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public CurrencyRate find(long key) throws SQLException {
		CurrencyRate l_CurrencyRate = null;
		query = getSelectQuery() + " where CurrencyRateKey = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_CurrencyRate = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_CurrencyRate;
	}

	@Override
	protected CurrencyRate readRecord(ResultSet rs) throws SQLException {
		CurrencyRate l_entity = new CurrencyRate();
		l_entity.setCurrencyRateId(rs.getLong("CurrencyRateKey"));
		l_entity.setCurrencyRateCode(rs.getString("CurrencyRate_ID"));
		l_entity.setDescription(rs.getString("Description"));
		l_entity.setSourceCurrencyId(rs.getLong("SourceCurrencyKey"));
		l_entity.setConvertCurrencyId(rs.getLong("ConvertCurrencyKey"));
		l_entity.setRoper(rs.getString("Roper"));
		l_entity.setBranchId(rs.getLong("Office_id"));
		return l_entity;
	}

	@Override
	public CurrencyRate findByCode(String code, long branchId)
			throws SQLException {
		CurrencyRate l_CurrencyRate = null;
		query = getSelectQuery() + " where CurrencyRate_ID = N'" + code
				+ "' and Office_id = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_CurrencyRate = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_CurrencyRate;
	}

	@Override
	public CurrencyRate find(long sourceCurrencyId, long convertCurrencyId,long branchId)
			throws SQLException {
		CurrencyRate l_CurrencyRate = null;
		query = "select top(1)* from " + table_name
				+ " where SourceCurrencyKey = " + sourceCurrencyId
				+ " and ConvertCurrencyKey = " + convertCurrencyId
				+ " and Office_id = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_CurrencyRate = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_CurrencyRate;
	}

}
